//===============================================================================
// Microsoft Architecture Strategy Team
// LitwareHR - SaaS Sample Application
//===============================================================================
// Copyright  Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
//===============================================================================



using System;

namespace LitwareHR.Recruiting.DataModel.Sql
{
    internal static class Constants
    {
        #region DB

        internal static class DB
        {
            internal const string TenantDataStore = "TenantDataStore";
            internal const string TenantMetadataStore = "TenantMetadataStore";

            internal const string TrackingStore = "TrackingStore";

            internal const string SqlForSelectRecruitingWorkflowListByWorkflowIdAndEventId = @"
                SELECT WorkflowInstanceId, EventId, FieldName, Data_Str 
                    FROM TrackingDataItem t 
                    JOIN WorkflowInstance w ON t.WorkflowInstanceInternalId = w.WorkflowInstanceInternalId

                WHERE
	                WorkflowInstanceId = '{0}'
	                AND EventId = {1}
                ";

            internal const string SqlForSelectRecruitingWorkflowIdsByApplicant = @"
                SELECT DISTINCT WorkflowInstanceId, EventId
	                FROM TrackingDataItem t 
	                JOIN WorkflowInstance w ON t.WorkflowInstanceInternalId = w.WorkflowInstanceInternalId

                WHERE
	                t.WorkflowInstanceInternalId IN
	                -- Filter Workflows by Tenant
	                (SELECT WorkflowInstanceInternalId FROM TrackingDataItem 
		                WHERE fieldName = 'tenantId' 	
			                AND Data_Str= '{0}')
	                AND
	                t.WorkflowInstanceInternalId IN
	                -- Filter Workflows by ApplicantUsername
	                (SELECT WorkflowInstanceInternalId FROM TrackingDataItem
		                WHERE fieldName = 'ApplicantUsername'
		                AND Data_Str = '{1}')
	                AND EventId IN
	                -- Select last event from workflow Instance.
	                (SELECT max(EventId) FROM TrackingDataItem WHERE WorkflowInstanceInternalId = t.WorkflowInstanceInternalId)
	                -- And instance has not Completed, Exception, Terminated or Aborted. View TrackingWorkflowEvent table.
	                AND t.WorkflowInstanceInternalId NOT IN
	                (SELECT WorkflowInstanceInternalId FROM WorkflowInstanceEvent  
		                WHERE TrackingWorkflowEventId IN (1, 8, 9, 10))
                ";

            internal const string SqlForSelectRecruitingWorkflowIds = @"
                SELECT DISTINCT WorkflowInstanceId, EventId
	                FROM TrackingDataItem t 
	                JOIN WorkflowInstance w ON t.WorkflowInstanceInternalId = w.WorkflowInstanceInternalId

                WHERE
	                t.WorkflowInstanceInternalId IN
	                -- Filter Workflows by Tenant
	                (SELECT WorkflowInstanceInternalId FROM TrackingDataItem 
		                WHERE fieldName = 'tenantId'
			                AND Data_Str= '{0}')
	                AND
	                t.WorkflowInstanceInternalId IN
	                -- Filter Workflows by ApplicantUsername
	                (SELECT WorkflowInstanceInternalId FROM TrackingDataItem
		                WHERE fieldName = 'ApplicantUsername')
	                AND EventId IN
	                -- Select last event from workflow Instance.
	                (SELECT max(EventId) FROM TrackingDataItem WHERE WorkflowInstanceInternalId = t.WorkflowInstanceInternalId)
	                -- And instance has not Completed, Exception, Terminated or Aborted. View TrackingWorkflowEvent table.
	                AND t.WorkflowInstanceInternalId NOT IN
	                (SELECT WorkflowInstanceInternalId FROM WorkflowInstanceEvent  
		                WHERE TrackingWorkflowEventId in (1, 8, 9, 10))
               ";

            internal const string SqlSubmitResume = "SubmitResume";
            internal const string SqlGetResume = "GetResume";
            internal const string SqlGetSilverlightAccount = "GetSilverlightAccountById";
            internal const string SqlAddVideoResume = "AddVideoResume";
            internal const string SqlSetErrorVideoUpload = "SetErrorVideoUpload";
        }

        #endregion
       
    }
}
